package org.execute.tool.excel转为viso文件;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

public class ExcelRangeExtractor {

    /**
     * 从Excel指定范围提取数据，组成二维数组
     *
     * @param excelFilePath Excel文件路径
     * @param sheetName 工作表名称
     * @param startRow 起始行索引（0-based）
     * @param startCol 起始列索引（0-based）
     * @param rowCount 要提取的行数
     * @param colCount 要提取的列数
     * @return 包含提取数据的二维字符串数组
     */
    public static String[][] extractRangeAsArray(String excelFilePath, String sheetName,
                                                 int startRow, int startCol,
                                                 int rowCount, int colCount) {
        // 初始化二维数组
        String[][] dataArray = new String[rowCount][colCount];
        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            inputStream = new FileInputStream(excelFilePath);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);

            if (sheet == null) {
                System.err.println("工作表 " + sheetName + " 不存在");
                return dataArray;
            }

            // 遍历指定范围的行和列
            for (int i = 0; i < rowCount; i++) {
                int currentRowIndex = startRow + i;
                Row row = sheet.getRow(currentRowIndex);

                for (int j = 0; j < colCount; j++) {
                    int currentColIndex = startCol + j;
                    Cell cell = (row != null) ? row.getCell(currentColIndex) : null;
                    dataArray[i][j] = getCellValueAsString(cell);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return dataArray;
    }

    /**
     * 提取整个工作表的数据为二维数组
     *
     * @param excelFilePath Excel文件路径
     * @param sheetName 工作表名称
     * @return 包含整个工作表数据的二维字符串数组
     */
    public static String[][] extractAllAsArray(String excelFilePath, String sheetName) {
        FileInputStream inputStream = null;
        Workbook workbook = null;
        int rowCount = 0;
        int colCount = 0;

        try {
            inputStream = new FileInputStream(excelFilePath);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(sheetName);

            if (sheet == null) {
                System.err.println("工作表 " + sheetName + " 不存在");
                return new String[0][0];
            }

            // 获取实际行数和最大列数
            rowCount = sheet.getLastRowNum() + 1;
            int maxColCount = 0;
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    int currentColCount = row.getLastCellNum();
                    if (currentColCount > maxColCount) {
                        maxColCount = currentColCount;
                    }
                }
            }
            colCount = maxColCount;

            // 关闭当前流，准备重新读取
            workbook.close();
            inputStream.close();

            // 使用获取到的行列数提取数据
            return extractRangeAsArray(excelFilePath, sheetName, 0, 0, rowCount, colCount);

        } catch (IOException e) {
            e.printStackTrace();
            return new String[0][0];
        }
    }

    /**
     * 将单元格内容转换为字符串
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(cell.getDateCellValue());
                } else {
                    // 处理数字，避免科学计数法
                    DecimalFormat df = new DecimalFormat("#.##########");
                    return df.format(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                // 对于公式，获取计算结果
                return evaluateCellFormula(cell);
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    /**
     * 计算公式单元格的值
     */
    private static String evaluateCellFormula(Cell cell) {
        Workbook workbook = cell.getSheet().getWorkbook();
        CreationHelper helper = workbook.getCreationHelper();
        FormulaEvaluator evaluator = helper.createFormulaEvaluator();

        CellValue cellValue = evaluator.evaluate(cell);

        switch (cellValue.getCellType()) {
            case STRING:
                return cellValue.getStringValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(cell.getDateCellValue());
                } else {
                    DecimalFormat df = new DecimalFormat("#.##########");
                    return df.format(cellValue.getNumberValue());
                }
            case BOOLEAN:
                return String.valueOf(cellValue.getBooleanValue());
            default:
                return "";
        }
    }

    // 测试方法
    public static void main(String[] args) {
        String excelFilePath = "E:\\工作记录\\2025_1\\fb 导出数据库2025年1月11日192837.xlsx";
        String sheetName = "fb 导出数据库";

        // 示例1：提取指定范围（从第0行第0列开始，提取5行3列）
        String[][] rangeData = extractRangeAsArray(excelFilePath, sheetName, 0, 0, 5, 3);
        System.out.println("指定范围数据:");
        for (String[] row : rangeData) {
            for (String cell : row) {
                System.out.print(cell + "\t");
            }
            System.out.println();
        }

        // 示例2：提取整个工作表数据
        String[][] allData = extractAllAsArray(excelFilePath, sheetName);
        System.out.println("\n整个工作表数据 (前5行):");
        int displayRows = Math.min(allData.length, 5);
        for (int i = 0; i < displayRows; i++) {
            for (String cell : allData[i]) {
                System.out.print(cell + "\t");
            }
            System.out.println();
        }
    }
}
    